Technical Field
The present invention relates to database management and, more particularly, to techniques for optimizing database transactions on a multi-level storage system.
Description of the Related Art
Modern secondary storage devices include magnetic hard disks (HDs) and solid state drives (SSDs). To store a fixed amount of data, magnetic disks are the most cost-effective option. However, because input/output (I/O) operations induce physical disk head movements, the latency and throughput of HDs can be orders of magnitude worse than SSD, particularly for random I/O.
If the data set is too large to economically fit in solid SSDs alone, however, a given database may be split such that frequently used data resides on solid state storage, while less frequently used data resides on magnetic disks. In this setting, if multiple transactions are executed, there might be contention for requested records. This problem arises when a transaction gets a lock on an SSD record and awaits completion of an I/O request from the slow HDs, when another transaction already has the locks on other records and is waiting for the completion of the slow device's I/O transaction to access the SSD records. This causes underutilization of the fast device because locking contention reduces the overall system performance.
Naïve approaches to solving this problem may include, for example, involve forcing transactions that are mixed between fast and slow items to always access the slow items first, before locking any fast items. However, this approach has several drawbacks. First, it is unclear from the start of a transaction exactly what items will be needed. Some logic may specify, for example, that an item is needed only some of the time. Second, it may not be clear to the transaction which items are fast and which are slow, particularly if caching to the SSD is performed dynamically.
An alternative is to abort mixed calls in favor of purely SSD calls, but this incurs the overhead of aborting and restarting transactions, and furthermore risks starvation of mixed transactions when there are many SSD-only transactions being performed.